package com.samson.empapp.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.samson.empapp.dbo.LocationsDbo;
import com.samson.empapp.util.DbUtil;

public class LocationsDao implements ILocationsDao {
	
	static Logger logger = Logger.getLogger(LocationsDao.class);

	@Override
	public LocationsDbo findById(int id) {
		LocationsDbo obj = null;
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sb = null;
		try {
		sb = new StringBuilder();
		sb.append("SELECT LOCATION_ID as locations, STREET_ADDRESS as streetAddress, POSTAL_CODE as postalCode, CITY as city, STATE_PROVINCE as stateProvince, COUNTRY_ID as countryId "); 
		sb.append("FROM locations "); 
		sb.append("where LOCATION_ID='?' ");
			pstmt = con.prepareStatement(sb.toString());
			logger.info("Executing Query " + sb.toString());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				obj = new LocationsDbo();
				obj.setLocationId(rs.getInt("locationId"));
				obj.setStreetAddress(rs.getString("streetAddress"));
				obj.setPostalCode(rs.getString("postalCode"));
				obj.setCity(rs.getString("city"));
				obj.setStateProvince(rs.getString("stateProvince"));
				obj.setCountryId(rs.getString("countryId"));
										
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.error("Exception" + e);
		} finally {
			DbUtil.closeDBConnection(pstmt, rs, con, sb);
		}
		

		return obj;
}

	@Override
	public List<LocationsDbo> findAll() {
		List<LocationsDbo> returnList = new ArrayList<LocationsDbo>();
		LocationsDbo obj = null;
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("SELECT LOCATION_ID as locations, STREET_ADDRESS as streetAddress, POSTAL_CODE as postalCode, CITY as city, STATE_PROVINCE as stateProvince, COUNTRY_ID as countryId "); 
			sb.append("FROM locations ");
			pstmt = con.prepareStatement(sb.toString());
			logger.info("Executing Query " + sb.toString());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				obj = new LocationsDbo();
				obj.setLocationId(rs.getInt("locationId"));
				obj.setStreetAddress(rs.getString("streetAddress"));
				obj.setPostalCode(rs.getString("postalCode"));
				obj.setCity(rs.getString("city"));
				obj.setStateProvince(rs.getString("stateProvince"));
				obj.setCountryId(rs.getString("countryId"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.error("Exception" + e);
		} finally {
			DbUtil.closeDBConnection(pstmt, rs, con, sb);
		}

		return returnList;

	}

	@Override
	public void insert(LocationsDbo dbo) throws Exception {
		LocationsDbo obj=null;
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("INSERT INTO \"HR\".\"LOCATIONS\" "); 
			sb.append("(LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) VALUES "); 
			sb.append("('"+obj.getLocationId()+"' /*not nullable*/,'"+obj.getStreetAddress()+"','"+obj.getPostalCode()+"','"+obj.getCity()+"' /*not nullable*/,'"+obj.getStateProvince()+"','"+obj.getCountryId()+"') ");
			pstmt = con.prepareStatement(sb.toString());
			logger.info("Executing Query " + sb.toString());
			pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("Exception" + e);
			throw e;

		} finally {
			DbUtil.closeDBConnection(pstmt, con, sb); 
	}

	}
	}
